<?php

	session_start();
	if(!($_SESSION["username"])||($_SESSION["role"]!="TUT")){
		//not logged in or tutor
		header("location:index.php");
	}
	
	if (isset($_GET['file'])){
			$filename=$_GET['file'];
			
		//Get Document Path
		$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"])));
		$OpenFile = "temp/".$filename;
		// Create Excel.Application 
		$xlApp = new COM("Excel.Application");
		$xlBook = $xlApp->Workbooks->Open($strPath."/".$OpenFile);

		$xlSheet1 = $xlBook->Worksheets(1);

		//Insert to MySQL Database
		$objConnect = mysql_connect("localhost","root","") or die(mysql_error());
		$objDB = mysql_select_db("Project 5.5");
		
		$sql="SELECT User_ID FROM users";
		$result=mysql_query($sql);
		
		$count=0;
		$currentIDs=null;
		
		while ($row = mysql_fetch_row($result)){
			$currentIDs[$count]=$row[0];			
			$count++;
		}
		
		$sql="SELECT User_ID FROM users WHERE Role='TUT'";
		$result=mysql_query($sql);
		
		$count=0;
		$currentTutorIDs=null;
		
		while ($row = mysql_fetch_row($result)){
			$currentTutorIDs[$count]=$row[0];			
			$count++;
		}

		$i=2;
		while(($xlSheet1->Cells->Item($i,2) != "")){
			echo ">>>".$xlSheet1->Cells->Item($i,2)."<br/>";
			$SQL = "";
			$SQL2 = "";
			if (in_array("".$xlSheet1->Cells->Item($i,1),$currentTutorIDs) && ($xlSheet1->Cells->Item($i,1) != "")){//is tutor
				$SQL = "UPDATE users SET ";
				$SQL .= "First_Name='".$xlSheet1->Cells->Item($i,2)."', ";
				$SQL .= "Last_Name='".$xlSheet1->Cells->Item($i,3)."', Username='".$xlSheet1->Cells->Item($i,4)."', ";
				$SQL .= "Password='".$xlSheet1->Cells->Item($i,5)."', Email='".$xlSheet1->Cells->Item($i,6)."', ";
				$SQL .= "WHERE User_ID='".$xlSheet1->Cells->Item($i,1)."'";
				
				$SQL2 = "UPDATE classes SET ";
				$SQL2 .= "Tutor='".$xlSheet1->Cells->Item($i,1)."'";
				$SQL2 .= "WHERE Class_ID='".$xlSheet1->Cells->Item($i,7)."' ";
				mysql_query($SQL);
				mysql_query($SQL2);
			} else {
				if ( (!in_array("".$xlSheet1->Cells->Item($i,1),$currentIDs))  ) {//not current user
					$SQL = "INSERT INTO users (";
					if ($xlSheet1->Cells->Item($i,1) != "") {
						$SQL .= "User_ID,";
					}
					$SQL .= "First_Name,Last_Name,Username,Password,Email,Role) ";
					$SQL .= "VALUES ('";
					if ($xlSheet1->Cells->Item($i,1) != "") {
						$SQL .= $xlSheet1->Cells->Item($i,1)."','";
					}
					$SQL .= $xlSheet1->Cells->Item($i,2)."' ";
					$SQL .= ",'".$xlSheet1->Cells->Item($i,3)."','".$xlSheet1->Cells->Item($i,4)."' ";
					$SQL .= ",'".$xlSheet1->Cells->Item($i,5)."','".$xlSheet1->Cells->Item($i,6)."' ";
					$SQL .= ",'TUT');";
					echo $SQL."<br/>";
					mysql_query($SQL);
					$SQL="SELECT User_ID FROM users WHERE Username='".$xlSheet1->Cells->Item($i,4)."';";
					$result=mysql_fetch_row(mysql_query($SQL));
					
					$SQL2 = "UPDATE classes SET ";
					$SQL2 .= "Tutor='".$result[0]."'";
					$SQL2 .= "WHERE Class_ID='".$xlSheet1->Cells->Item($i,7)."' ";
					mysql_query($SQL2);
				}
			}
			$i++;
		}
		
		//Close & Quit
		mysql_close($objConnect);
		$xlApp->Application->Quit();
		$xlApp = null;
		$xlBook = null;
		$xlSheet1 = null;
		
		header("location:managetutors.php?message=delete&file=".$_GET['file']);
	}
?>